package dao.impl;


import dao.SalesDao;
import domain.Sales;
import util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class SalesImpl implements SalesDao {
    @Override
    public List<Sales> search(String year, String month) {
        String sql = "SELECT products.name, CAST(SUM(orderitem.buynum) AS SIGNED) buynum "
                + "FROM orders, products, orderItem "
                + "WHERE orders.id = orderItem.order_id "
                + "AND products.id = orderItem.product_id AND orders.paystate = 1 "
                + "AND year(ordertime) = ? and month(ordertime) = ? "
                + "GROUP BY products.name ORDER BY buynum DESC";
        List<Sales> ps = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setString(1, year);
            statement.setString(2, month);
            resultSet = statement.executeQuery();
            Sales sales;
            while ((sales = JDBCUtil.parseResultSet(resultSet, Sales.class)) != null) {
                ps.add(sales);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(connection, statement, resultSet);
        }
        return ps;
    }
}
